package org.openea.eap.module.obpm.service.custsql.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.tree.Tree;
import cn.hutool.core.lang.tree.TreeNode;
import cn.hutool.core.lang.tree.TreeUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.NumberUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang3.StringUtils;
import org.openbpm.base.api.response.impl.ResultMsg;
import org.openbpm.base.core.util.BeanUtils;
import org.openbpm.base.core.util.ExceptionUtil;
import org.openbpm.base.core.util.StringUtil;
import org.openea.eap.module.obpm.model.form.constants.FieldsConstants;
import org.openea.eap.module.obpm.model.custsql.vo.*;
import org.openea.eap.module.obpm.service.custsql.FormCustSqlFieldsManager;
import org.openea.eap.module.obpm.service.custsql.FormCustSqlFlowExtService;
import org.openea.eap.module.obpm.service.custsql.FormCustSqlManager;
import org.openea.eap.module.obpm.service.custsql.FormCustSqlViewManager;
import org.openea.eap.module.obpm.model.custsql.FormCustSql;
import org.openea.eap.module.obpm.model.custsql.FormCustSqlFields;
import org.openea.eap.module.obpm.model.custsql.SqlViewRequest;
import org.openbpm.org.api.model.IUser;
import org.openbpm.org.api.service.UserService;
import org.openbpm.sys.api.freemark.IFreemarkerEngine;
import org.openbpm.sys.api.groovy.IGroovyScriptEngine;
import org.openbpm.sys.api.model.ISysTreeNode;
import org.openbpm.sys.api.model.dto.DataDictDTO;
import org.openbpm.sys.api.service.DataDictService;
import org.openbpm.sys.api.service.ISysTreeNodeService;
import org.openea.eap.module.obpm.util.custsql.JSONObjectUtil;
import org.openea.eap.module.obpm.util.custsql.JdbcTemplateUtil;
import org.openea.eap.module.obpm.util.custsql.SqlUtil;
import org.openea.eap.module.obpm.util.custsql.ViewUtil;
import org.openea.eap.module.obpm.util.form.ArrayUtil;
import org.openea.eap.module.obpm.util.form.DateUtils;
import org.openea.eap.module.obpm.util.form.JxlUtils;
import org.openea.eap.module.obpm.util.form.Util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestParam;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.*;
import java.util.stream.Collectors;
import java.lang.Boolean;


@Primary
@Service("formCustSqlViewManager")
public class FormCustSqlViewManagerImpl
        implements FormCustSqlViewManager {
    protected Logger logger = LoggerFactory.getLogger(getClass());


    @Resource
    FormCustSqlManager formCustSqlManager;

    @Autowired
    ISysTreeNodeService sysTreeNodeService;


    @Resource
    FormCustSqlFieldsManager formCustSqlFieldsManager;


    @Autowired
    IFreemarkerEngine freemarkerEngine;


    @Resource
    DataDictService dataDictService;

    @Autowired
    IGroovyScriptEngine groovyScriptEngine;

    @Resource
    UserService userService;

    @Resource
    FormCustSqlFlowExtService formCustSqlFlowExtService;

    private JSONObject toJson(FormCustSql formCustSql){
        return formCustSqlManager.toJson(formCustSql);
    }
    public ResultMsg<JSONObject> getVoJson(@RequestParam String code) {
        ResultMsg<FormCustSql> result = getVo(code);
        FormCustSql formCustSql = result.getData();
        JSONObject json = toJson(formCustSql);
        return ResultMsg.SUCCESS(json);
    }

    public ResultMsg<FormCustSql> getVo(String code) {

        // 0 提前参数检查
        if (StringUtil.isEmpty(code)) {
            return ResultMsg.ERROR("ID不能为空");
        }

        boolean isAdvanced = false;

        // 1 获取配置及初始化
        FormCustSql formCustSql = this.formCustSqlManager.getByCode(code, true);
        if (formCustSql == null) {
            return ResultMsg.ERROR("自定义列表不存在,code:" + code);
        }

        if (0 == formCustSql.getStatus().intValue()) {
            return ResultMsg.ERROR("自定义列表已被禁用");
        }

        isAdvanced = "1".equals(JSONObjectUtil.getValByKey("isAdvanced", formCustSql.getExpand()));

        formCustSql.setSqlContent(null);
        formCustSql.setCreateBy(null);
        formCustSql.setCreateTime(null);
        formCustSql.setCreator(null);
        formCustSql.setUpdateBy(null);
        formCustSql.setUpdateTime(null);
        formCustSql.setUpdator(null);
        formCustSql.setTableName(null);
        formCustSql.setDsKey(null);


        List<FormCustSqlFields> fieldsList = formCustSql.getFieldsList();

        // 2 按钮配置 buttons
        String buttons = formCustSql.getButtons();
        if (StringUtil.isNotEmpty(buttons)) {
            List<SqlButtonVO> buttonList = JSONObjectUtil.toList(buttons, SqlButtonVO.class);
            for (SqlButtonVO sqlButtonVO : buttonList) {
                sqlButtonVO.setDataChange(null);
            }
            formCustSql.setButtonList(buttonList);
            formCustSql.setButtons(JSONArray.toJSONString(buttonList));
        }

        // 显示条件过滤
        Map<String, Object> groovyParam = new HashMap();
        groovyParam.put("formCustSql", formCustSql);
        for (FormCustSqlFields fieldSetting : fieldsList) {
            String showCondition = JSONObjectUtil.getValByKey("showCondition", fieldSetting.getExpand());
            if (StringUtil.isNotEmpty(showCondition)) {
                try {
                    groovyParam.put("fieldSetting", fieldSetting);
                    boolean show = groovyScriptEngine.executeBoolean(showCondition, groovyParam);
                    if (!show) {
                        fieldSetting.setHidden(1);
                    }
                } catch (Exception e) {
                    fieldSetting.setHidden(1);
                }
            }
        }

        // 3 数据字典 dicMap
        // 高级模式下 数据字典内容返回为JSONArray[{value,label,active,style,link,ex1,ex2,ex3}] 数组已排好序
        Map<String, Object> dicMap = new HashMap();
        // 3.1 检查条件列表中所用数据字典
        List<SqlConditionVO> conditionList = JSONObjectUtil.toList(formCustSql.getConditions(), SqlConditionVO.class);
        if (!CollectionUtils.isEmpty(conditionList)) {
            Iterator it = conditionList.iterator();

            while (it.hasNext()) {
                SqlConditionVO sqlConditionVO = (SqlConditionVO) it.next();
                this.getDicMap(dicMap, sqlConditionVO.getCtltype(), sqlConditionVO.getJson(), isAdvanced);
            }
        }
        // 3.2 检查字段配置中所用字典
        if (!CollectionUtils.isEmpty(fieldsList)) {
            Iterator it = fieldsList.iterator();
            while (it.hasNext()) {
                FormCustSqlFields formCustSqlFields = (FormCustSqlFields) it.next();
                Integer controlType = formCustSqlFields.getControlType();
                String format = formCustSqlFields.getDateFormat();  //dataFormat
                this.getDicMap(dicMap, controlType, format, isAdvanced);
            }
        }
        // 3.3 左侧树数据字典 left tree dic map
        this.getLeftTreeDicMap(formCustSql, dicMap, isAdvanced);
        formCustSql.setDicMap(dicMap);

        return ResultMsg.SUCCESS(formCustSql);
    }

    private void getLeftTreeDicMap(FormCustSql formCustSql, Map<String, Object> dicMap, boolean isAdvanced) {
//        String expand = formCustSql.getExpand();
//        String treeDataType = JSONObjectUtil.getValByKey("tree.dataType", expand);
//        String treeDateFormat = JSONObjectUtil.getValByKey("tree.dateFormat", expand);
        JSONObject expand = JSONObjectUtil.parseExpand(formCustSql.getExpand());
        String treeDataType = expand.getString("tree.dataType");
        String treeDateFormat = expand.getString("tree.dateFormat");
        if (StringUtil.isEmpty(treeDataType) || StringUtil.isEmpty(treeDateFormat)) {
            return;
        }

        if (treeDataType.equals("2")) {
            // 数据字典
            this.getDicMap(dicMap, FieldsConstants.DIC, treeDateFormat, isAdvanced);
        } else if (treeDataType.equals("3")) {
            List<? extends ISysTreeNode> treeNodeList = this.sysTreeNodeService.getTreeNodesByType(treeDateFormat);
            if (!CollUtil.isEmpty(treeNodeList)) {
                JSONObject dicJo = new JSONObject();
                dicMap.put(treeDateFormat, dicJo);
                if (!CollectionUtils.isEmpty(treeNodeList)) {
                    Iterator var21 = treeNodeList.iterator();

                    while (var21.hasNext()) {
                        ISysTreeNode iSysTreeNode = (ISysTreeNode) var21.next();
                        dicJo.put(iSysTreeNode.getKey(), iSysTreeNode.getName());
                    }
                }
            }
        } else if (treeDataType.equals("4")) {
            SqlViewRequest request = new SqlViewRequest();
            request.setPage(1);
            request.setPageSize(Integer.MAX_VALUE);
            request.setCode(treeDateFormat);
            ResultMsg<SqlViewDataVO> listData = this.listData(request);
            if (listData.getIsOk()) {
                JSONArray list = (JSONArray) ((SqlViewDataVO) listData.getData()).getList();
                String custSqlId = JSONObjectUtil.getValByKey("tree.custSqlId", expand);
                String custSqlPid = JSONObjectUtil.getValByKey("tree.custSqlPid", expand);
                String custSqlTitle = JSONObjectUtil.getValByKey("tree.custSqlTitle", expand);

                // 先转树过滤掉不匹配的数据，并增加指定TOP的功能
                String rootId = JSONObjectUtil.getValByKey("tree.rootId", expand);
                // 构建node列表
                List<TreeNode<String>> nodeList = CollUtil.newArrayList();

                JSONArray dicList = new JSONArray();
                Iterator var16 = list.iterator();
                while (var16.hasNext()) {
                    Object object = var16.next();
                    JSONObject jObject = (JSONObject) object;

                    String id = jObject.getString(custSqlId);
                    String parentId = jObject.getString(custSqlPid);
                    String name = jObject.getString(custSqlTitle);
                    Long weight = 0l;
                    try{
                        weight = Long.parseLong(jObject.getString("orderby"));
                    }catch (Exception e){
                    }
                    nodeList.add(new TreeNode<>(id, parentId, name, weight));
                }
                // 构建根节点
                List<Tree<String>> treeList = TreeUtil.build(nodeList, rootId);
                tree2List(treeList, dicList);
                dicMap.put("custsql_" + treeDateFormat, dicList);
            }
        }

    }

    private void tree2List(List<Tree<String>> treeList, JSONArray dicList){
        for(Tree<String> tree: treeList){
            JSONObject dicJo = new JSONObject();
            dicJo.put("key", tree.getId());
            dicJo.put("pid", tree.getParentId());
            dicJo.put("title", tree.getName());
            dicList.add(dicJo);
            if(tree.getChildren()!=null){
                tree2List(tree.getChildren(), dicList);
            }
        }
    }

    private void getDicMap(Map<String, Object> dicMap, Integer controlType, String format) {
        getDicMap(dicMap, controlType, format, false);
    }

    private void getDicMap(Map<String, Object> dicMap, Integer controlType, String dictKey, boolean isAdanced) {
        if (dicMap.containsKey(dictKey)) {
            return;
        }
        // dict 5,7,9,18 （不包含由后台提供的代码表）
        if (!Util.list(new Integer[]{FieldsConstants.DIC, FieldsConstants.DIC_RADIO, FieldsConstants.DIC_CHECKBOX}).contains(controlType)) {
            return;
        }
        List<DataDictDTO> dictNodeList = this.dataDictService.getDictNodeList(dictKey, false);
        // 兼容旧版本 返回 JSONObject
        JSONObject dicJo = new JSONObject();
        dicMap.put(dictKey, dicJo);
        if (!CollectionUtils.isEmpty(dictNodeList)) {
            Iterator it = dictNodeList.iterator();
            while (it.hasNext()) {
                DataDictDTO dic = (DataDictDTO) it.next();
                dicJo.put(dic.getKey(), dic.getName());
            }
        }
        // 新版本增加返回 JSONArray
        // key = a_[dictKey]
        if (isAdanced) {
            JSONArray dicArray = new JSONArray();
            if (!CollectionUtils.isEmpty(dictNodeList)) {
                for(DataDictDTO dic:dictNodeList){
                    dicArray.add(DataDictDTO.toJson(dic));
                }
            }
            dicMap.put("a_"+dictKey, dicArray);
        }
    }

    public ResultMsg<SqlViewDataVO> listData(SqlViewRequest request) {
        // 增加执行性能监控
        long t0 = System.currentTimeMillis();
        long cost = 0;  //seconds
        // 0. 参数
        String code = request.getCode();
        Integer curPage = Integer.valueOf(request.getCurPage());
        Integer pageSize = request.getPageSize();

        // 1. 准备SQL
        ResultMsg<SqlViewVO> listDataSql = listDataSql(request);
        SqlViewVO sqlViewVO = (SqlViewVO) listDataSql.getData();

        String querySumSql = sqlViewVO.getQuerySumSql();
        String queryCountSql = sqlViewVO.getQueryCountSql();
        String queryDataSql = sqlViewVO.getQueryDataSql();

        cost = (System.currentTimeMillis() - t0) / 1000;
        if (cost >= 1) {
            logger.warn("执行listDataSql, code={}, cost={}秒", code, cost);
        }

        // 2. 查询数据 query data
        // 2.1 sql query
        String dsKey = sqlViewVO.getDsKey();
        JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);
        long t1 = System.currentTimeMillis();
        List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(queryDataSql);
        cost = (System.currentTimeMillis() - t1) / 1000;
        if (cost >= 1) {
            logger.warn("执行queryDataSql, code={}, cost={}秒 \r\n sql={}", code, cost, queryDataSql);
        }

        // 2.2 流程列表/任务列表处理
        // 检查关联流程配置 flow, task
        String bpmExt = sqlViewVO.getBpmExt();
        if ("bpm_flow_ext".equalsIgnoreCase(bpmExt)) {
            queryForList = formCustSqlFlowExtService.extFlowData(queryForList, sqlViewVO);
        }
        if (bpmExt != null && (bpmExt.startsWith("todotask_") || bpmExt.startsWith("dealtask_"))) {
            queryForList = formCustSqlFlowExtService.extTaskData(queryForList, sqlViewVO, bpmExt);
        }

        // 2.3 定制数据查询
        try{
            queryForList = custQueryData(queryForList, sqlViewVO, code);
        }catch (Exception  e){
            e.printStackTrace();
            logger.error("custQueryData error",e);
        }

        // 2.4 树状列表 treeList 处理
        try{
            queryForList = handleData4Treelist(queryForList, sqlViewVO);
        }catch(Exception  e){
            e.printStackTrace();
            logger.error("handleData4Treelist error",e);
        }

        // 3. 数据处理
        // 3.1 后台代码表处理/用户信息
        handleDataWithDict(queryForList, sqlViewVO);

        // 3.2 数据后处理
        String list = ViewUtil.changeQueryListToJSONStr(queryForList);
        Object listData = ViewUtil.handleData(code, list, jdbcTemplate, sqlViewVO);

        // 4. 结果准备以及统计数据
        // 4.0 结果准备
        SqlViewDataVO sqlViewDataVO = new SqlViewDataVO();
        sqlViewDataVO.setList(listData);

        // 4.1 分页检查
        int count = 0;
        if (queryForList.size() < pageSize.intValue()) {
            count = (curPage.intValue() - 1) * pageSize.intValue() + queryForList.size();
        }
        // 4.2 sum检查及执行
        if (StringUtils.isNotEmpty(querySumSql)) {
            long t2 = System.currentTimeMillis();
            try {
                Map<String, Object> mSumData = jdbcTemplate.queryForMap(querySumSql);
                sqlViewDataVO.setSumData(mSumData);
                if (count == 0) {
                    count = Integer.valueOf("" + mSumData.get("counts"));
                }
            } catch (Exception e) {
                logger.error("querySum:" + e.getMessage() + "\r\n sql=" + querySumSql, e);
                //e.printStackTrace();
            }
            cost = (System.currentTimeMillis() - t2) / 1000;
            if (cost >= 1) {
                logger.warn("执行querySumSql , code={}, cost={}秒 \r\n sql={}", code, cost, querySumSql);
            }
        }
        // 4.3 count检查及执行
        if (count == 0 && queryForList.size() > 0 && !StringUtils.isBlank(queryCountSql)) {
            long t3 = System.currentTimeMillis();
            count = ((Integer) jdbcTemplate.queryForObject(queryCountSql, Integer.class)).intValue();
            cost = (System.currentTimeMillis() - t3) / 1000;
            if (cost >= 1) {
                logger.warn("执行queryCountSql , code={}, cost={}秒 \r\n sql={}", code, cost, queryCountSql);
            }
        }
        if(count==0 && queryForList.size()>0){
            count = queryForList.size();
        }
        sqlViewDataVO.setCount(Integer.valueOf(count));

        // 检查整个方法执行时间
        cost = (System.currentTimeMillis() - t0) / 1000;
        if (cost >= 1) {
            logger.warn("listData 查询完成, code={}, cost={}秒 \r\n sql={}", code, cost, queryDataSql);
        }
        return ResultMsg.SUCCESS(sqlViewDataVO);
    }

    List<Map<String, Object>> custQueryData(List<Map<String, Object>> queryForList, SqlViewVO sqlViewVO, String code){
        String custQueryBeanMethod = sqlViewVO.getCustQueryBeanMethod();
        // TODO 通过定制的数据服务 bean.method 来增强或替换 SQL 查询结果
        if (StringUtil.isNotEmpty(custQueryBeanMethod)){
            // 拆分 bean 和 method, 动态调用
        }
        return queryForList;
    }

    private void handleDataWithDict(List<Map<String, Object>> queryForList, SqlViewVO sqlViewVO) {
        // 后台字典表和字典表扩展
        // 包含人员扩展，特殊的后台代码表
        Map<String, Map<Object, DataDictDTO>> dicMap = new HashMap(); // Map<fieldKey, Map<value, Dict>>
        Map<String, String> userMap = new HashMap(); // Map<fieldKey, format>
        List<FormCustSqlFields> commonFieldList = sqlViewVO.getFieldList();
        for (FormCustSqlFields field : commonFieldList) {
            // 16 - 用户
            if (FieldsConstants.USER == field.getControlType()) {
                String fieldKey = field.getFieldName();
                String format = field.getDateFormat();
                if(StringUtils.isEmpty(format)){
                    // TODO 读取 styleFormat
                }
                if(StringUtils.isEmpty(format)){
                    format = "0"; //默认
                }
                userMap.put(fieldKey, format);
            }
            // 18 - 数据字典-扩展
            if (FieldsConstants.DIC2 == field.getControlType()) {
                String fieldKey = field.getFieldName();
                String dictKey = field.getDateFormat();
                List<DataDictDTO> dicts = this.dataDictService.getDictNodeList(dictKey, false);

                //List<DataDict> dicts = dataDictManager.getDictNodeList(dictKey, false);
                if (dicts != null && !dicts.isEmpty()) {
                    Map<Object, DataDictDTO> mapDictValue = new HashMap<>();
                    for (DataDictDTO dict : dicts) {
                        mapDictValue.put(dict.getKey(), dict);
                    }
                    dicMap.put(fieldKey, mapDictValue);
                }
            }
        }
        // 数据处理
        for (Map rowData : queryForList) {
            // 用户信息
            for (String filedKey : userMap.keySet()) {
                if (!rowData.containsKey(filedKey)) {
                    continue;
                }
                // 用户控件显示格式  2-头像，3-签名，其他默认
                String format = userMap.get(filedKey);
                String value = String.valueOf(rowData.get(filedKey));
                JSONArray userArray = new JSONArray();
                // 检查是否多个值用逗号分隔
                String[] valueMulti = value.split(",");
                if (valueMulti.length > 1) {
                    for (String value2 : valueMulti) {
                        userArray.add(getUserExt(value2, format));
                    }
                }else{
                    userArray.add(getUserExt(value, format));
                }
                rowData.put(filedKey + "_userext", userArray);
            }
            // 后台代码表
            for (String filedKey : dicMap.keySet()) {
                if (!rowData.containsKey(filedKey)) {
                    continue;
                }
                Map<Object, DataDictDTO> mapDictValue = dicMap.get(filedKey);
                String value = String.valueOf(rowData.get(filedKey));
                String label = null;
                // 检查是否多个值用逗号分隔
                String[] valueMulti = value.split(",");
                if (valueMulti.length > 1) {
                    for (String value2 : valueMulti) {
                        if (mapDictValue.containsKey(value2)) {
                            if (label == null) {
                                label = mapDictValue.get(value2).getName();
                            } else {
                                label += "," + mapDictValue.get(value2).getName();
                            }
                        }
                    }
                    if (StringUtil.isNotEmpty(label)) {
                        rowData.put(filedKey + "_label", label);
                    }
                } else {
                    if (mapDictValue.containsKey(value)) {
                        DataDictDTO dictDto = (DataDictDTO) mapDictValue.get(value);
                        if (StringUtil.isNotEmpty(dictDto.getName())) {
                            rowData.put(filedKey + "_label", dictDto.getName());
                        }
                        // 此功能不建议使用，用户扩展已迁移到用户控件中实现
                        if (dictDto != null && dictDto.getExtData() != null) {
                            rowData.put(filedKey + "_extdata", dictDto.getExtData().clone());
                        }
                    }
                }
            } //for
        }
    }

    private List<Map<String, Object>> handleData4Treelist(List<Map<String, Object>> queryForList, SqlViewVO sqlViewVO) {
        if(queryForList==null || queryForList.isEmpty()){
            return queryForList;
        }
        // 检查全局参数? 假定只有设置了全局参数才会配置 "41 - treelist 树状字段"

        // 树状字段
        String treeFieldKey = null;
        String treeIdKey = "treeid";  // 树ID字段
        String treeParentIdKey = "treepid";  // 树父ID字段
        List<FormCustSqlFields> commonFieldList = sqlViewVO.getFieldList();
        for (FormCustSqlFields field : commonFieldList) {
            // 41 - treelist 树状字段
            if (FieldsConstants.TREE_LIST == field.getControlType()) {
                // 获取 id和pid字段，用于组装tree和生成treedata数据提供给前端
                treeFieldKey = field.getFieldName();
                treeIdKey = "treeid";  // 树ID字段
                treeParentIdKey = "treepid";  // 树父ID字段
            }
        }
        if(StringUtils.isBlank(treeFieldKey)){
            return queryForList;
        }
        // 检查treeIdKey和treeParentIdKey是否存在
        if(!queryForList.get(0).containsKey(treeIdKey)
                || !queryForList.get(0).containsKey(treeParentIdKey)){
            return queryForList;
        }

        // 准备treelist的tree结构数据
        List<RowDataTree> list = new ArrayList();
        for(Map<String, Object> rowData: queryForList){
            list.add(new RowDataTree(rowData, treeFieldKey, treeIdKey, treeParentIdKey));
        }
        // list 转换 tree
        List<RowDataTree> tree = BeanUtils.listToTree(list);


        List<Map<String, Object>> queryForList2 = new ArrayList(); // 按照树结构重新排序后列表
        // 准备treelist的tree结构数据
        Map<String, String[]> mTreeData = new HashMap<>();  // Map<treeId, [全路径]>

        // 重新排序并增加treedata
        prepareFieldTreeData(queryForList2, mTreeData, tree);

        return queryForList2;
    }

    private void prepareFieldTreeData(List<Map<String, Object>> queryForList2, Map<String, String[]> mTreeData, List<RowDataTree> tree){
        for(RowDataTree treeNode: tree){
            String id = treeNode.getId();
            String parentId = treeNode.getParentId();
            String treeData[] = null;
            if(!StringUtils.isBlank(parentId) && !"0".equals(parentId) && mTreeData.containsKey(parentId)){
                // 包含父节点， 父路径 + 当前
                List<String>  tempList = new ArrayList();
                tempList.addAll(Arrays.asList(mTreeData.get(parentId)));
                tempList.add(treeNode.getName() + "||" + id);
                treeData = tempList.toArray(new String[tempList.size()]);
            }else{
                treeData = new String[]{treeNode.getName() + "||" + id};
            }
            mTreeData.put(id, treeData);
            treeNode.getRowData().put(treeNode.getTreeFieldKey()+"_treedata", treeData);
            queryForList2.add(treeNode.getRowData());
            // childrenl
            if(!CollectionUtils.isEmpty(treeNode.getChildren())){
                prepareFieldTreeData(queryForList2, mTreeData, treeNode.getChildren());
            }
        }
    }

    private class RowDataTree implements org.openbpm.base.api.model.Tree<RowDataTree>{
        Map<String, Object> rowData;
        List<RowDataTree> children;
        String treeFieldKey;
        String treeIdKey;
        String treeParentIdKey;

        public RowDataTree(Map<String, Object> rowData, String treeFieldKey, String treeIdKey, String treeParentIdKey){
            this.rowData = rowData;
            this.treeFieldKey = treeFieldKey;
            this.treeIdKey = treeIdKey;
            this.treeParentIdKey = treeParentIdKey;
        }
        @Override
        public String getId() {
            return MapUtil.getStr(rowData, treeIdKey);
        }

        @Override
        public String getParentId() {
            return MapUtil.getStr(rowData, treeParentIdKey);
        }

        public String getName(){
            return MapUtil.getStr(rowData, treeFieldKey);
        }

        public Map<String, Object> getRowData(){
            return this.rowData;
        }

        public String getTreeFieldKey(){
            return this.treeFieldKey;
        }

        @Override
        public List<RowDataTree> getChildren() {
            return children;
        }

        @Override
        public void setChildren(List<RowDataTree> list) {
            this.children = list;
        }
    }

    private JSONObject getUserExt(String value, String format){
        JSONObject userJson = new JSONObject();
        // 用户控件显示格式
        // TODO 2-头像，3-签名，其他默认
        userJson.put("format",format);
        // 获取用户信息，获取本地缓存中用户信息
        IUser user = userService.getUserById(value);
        userJson.put("fullname", user.getFullname());
        userJson.put("account", user.getAccount());
        userJson.put("mobile", user.getMobile());
        userJson.put("photo", user.getPhoto());
        userJson.put("status", user.getStatus());
        userJson.put("uid",value);
        return userJson;
    }

    /**
     * 解析查询查询，构建SQL
     *
     * @param request
     * @return
     */
    public ResultMsg<SqlViewVO> listDataSql(SqlViewRequest request) {
        String code = request.getCode();

        // 1、 查询参数准备
        // 1.1 request参数处理
        // 分页数据
        Integer pageNo = Integer.valueOf(request.getCurPage());
        Integer pageSize = request.getPageSize();

        // 查询条件
        String queryData = request.getParams();
        JSONObject queryDataObject = JSONObject.parseObject(queryData);

        // 1.2 grid条件处理
        // 针对ag-grid参数特别处理
        mergeGirdParams2QueryData(queryDataObject, request);

        // 1.3 查询数据汇总
        SqlQueryDataVO sqlQueryDataVO = (SqlQueryDataVO) JSONObject.toJavaObject((JSON) queryDataObject, SqlQueryDataVO.class);
        Map<String, String> urlParams = ViewUtil.getUrlParams(queryData);
        sqlQueryDataVO.setParamsData(urlParams);

        // 1.4 查询定义以及字段信息
        FormCustSql viewVo = this.formCustSqlManager.getByCode(code, true);


        // 1.5 定制参数查询
        JSONObject expandJo = JSONObjectUtil.parseExpand(viewVo.getExpand());

        // 定制数据查询服务，指定springBean.method
        String custQueryBeanMethod = null;
        if("1".equals(JSONObjectUtil.getValByKey("custQueryFlag", expandJo))){
            custQueryBeanMethod = JSONObjectUtil.getValByKey("custQueryBeanMethod", expandJo);
        }
        // 定制流程列表或定制任务列表
        String bpmExt = JSONObjectUtil.getValByKey("bpmExt", expandJo);
        // 流程任务定制逻辑
        // 依赖定制任务表 bpm_task_opinion, 必须包含字段 plan_complete_day_
        if (bpmExt != null
                && (bpmExt.startsWith("todotask_") || bpmExt.startsWith("dealtask_"))) {
            // 增加判断 原来SQL有流程实例字段 bpminstid或bpm_inst_id
            String bpmInstField = null;
            List<FormCustSqlFields> fields = viewVo.getFieldsList();
            for (FormCustSqlFields field : fields) {
                String fieldName = field.getFieldName().toLowerCase();
                if (fieldName.indexOf("bpminstanceid") >= 0 || fieldName.indexOf("instanceid") >= 0 || fieldName.indexOf("bpm_inst_id") >= 0) {
                    bpmInstField = fieldName;
                    break;
                }
            }
            boolean withBizId = (bpmInstField == null);
            // 对性能优化有用，业务上无差别
            String taskSql = FormCustSqlFlowExtHelper.getBpmTaskSqlByExtType(bpmExt, withBizId);
            if (StringUtil.isNotEmpty(taskSql)) {
                String sqlContent = viewVo.getSqlContent();
                // fix 去掉SQL语句的最后的"分号"
                sqlContent = sqlContent.trim();
                if (sqlContent.endsWith(";")) {
                    sqlContent = sqlContent.substring(0, sqlContent.length() - 1);
                }
                // order by a.lastdate desc
                boolean orderByLastdate = false;
                int n1 = sqlContent.toLowerCase().indexOf("order by");
                if (n1 > 0) {
                    int n2 = sqlContent.toLowerCase().indexOf("lastdate", n1);
                    if (n2 > 0) {
                        for (FormCustSqlFields field : fields) {
                            if ("lastdate".equalsIgnoreCase(field.getFieldName())) {
                                orderByLastdate = true;
                                break;
                            }
                        }
                    }
                }
                // 定制优化逻辑，待补充
                if (withBizId) {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.biz_id_ = bus." + viewVo.getPkName() + " ORDER BY task.plan_complete_day_";
                } else {
                    sqlContent = "select task.*, bus.* " +
                            " from (" + taskSql + " ) task, (" + sqlContent + ") bus" +
                            " where task.inst_id_ = bus." + bpmInstField + " ORDER BY task.plan_complete_day_";
                }
                if (orderByLastdate) {
                    sqlContent += " order by bus.lastdate desc";
                }
                viewVo.setSqlContent(sqlContent);
            }
        }

        // 2 SQL分析及处理
        // 2.1 sql结构分析
        List<FormCustSqlFields> fieldList = viewVo.getFieldsList();
        SqlConstructVO sqlConstructVO = ViewUtil.getSqlConstruct(viewVo, sqlQueryDataVO);

        // 2.2 sql参数处理
        Map<String, Object> paramsMap = sqlConstructVO.getParamsMap();
        JSONObject freemarkerParams = sqlConstructVO.getFreemarkerParams();
        freemarkerParams.putAll(urlParams);


        SqlViewVO sqlViewVO = ViewUtil.getFieldColumnJo(fieldList, expandJo);

        if (StringUtil.isNotEmpty(bpmExt)) {
            sqlViewVO.setBpmExt(bpmExt);
            // columnBizId will used in extFlowData
            String columnBizId = viewVo.getPkName();
            sqlViewVO.setBpmExtColumnBizId(columnBizId);
        }
        if(StringUtil.isNotEmpty(custQueryBeanMethod)){
            sqlViewVO.setCustQueryBeanMethod(custQueryBeanMethod);
        }


        String sql = sqlConstructVO.getSql();
        // fix
        //sql = SqlUtil.fixTwoOrderBy(sql);
        sql = ViewUtil.replaceParams(sql);

        sql = this.freemarkerEngine.parseByString(sql, freemarkerParams);
        sql = this.freemarkerEngine.parseByString(sql, paramsMap);

        String queryCountSql = null;
        String queryDataSql = null;

        String realSqlColumn = sqlViewVO.getRealSqlColumn();
        String sumSqlColumn = sqlViewVO.getSumSqlColumn();

        // 检查是否为treelist  (expand.treelist == 1) 20221012
        boolean isTreelist = "1".equals(JSONObjectUtil.getValByKey("treelist", expandJo));
        if(isTreelist){
            //不分页，返回所有数据，最好不超过2K条，系统限制为3k条
            queryDataSql = ViewUtil.getQueryDataSql(realSqlColumn, sql, 1, 3000);
        }else{
            queryCountSql = SqlUtil.getCountSql(sql);
            queryDataSql = ViewUtil.getQueryDataSql(realSqlColumn, sql, pageNo.intValue(), pageSize.intValue());
        }

        String querySumSql = null;
        if (!StringUtils.isBlank(sumSqlColumn)){
            querySumSql = SqlUtil.getSumSql(sumSqlColumn, sql);
        }

        // 3 数据返回
        sqlViewVO.setFieldList(fieldList);
        if(!StringUtils.isBlank(queryCountSql)) {
            sqlViewVO.setQueryCountSql(queryCountSql);
        }
        sqlViewVO.setQueryDataSql(queryDataSql);
        sqlViewVO.setQuerySumSql(querySumSql);
        sqlViewVO.setDsKey(viewVo.getDsKey());
        sqlViewVO.setSqlName(viewVo.getName());
        sqlViewVO.setQueryData(sqlConstructVO.getQueryData());
        sqlViewVO.setParamsMap(paramsMap);

        // log
//        String busModule = JSONObjectUtil.getValByKey("busModule", expandJo);
//        if (StringUtil.isEmpty(busModule)) {
//            formCustSqlManager.checkModuleInFormCustSql(viewVo);
//            busModule = JSONObjectUtil.getValByKey("busModule", viewVo.getExpand());
//        }
//        if (logger.isInfoEnabled()) {
//            String ip = RequestUtil.getIpAddr(RequestHolder.getRequest());
//            logger.info("invoke custSqlView, code={}, module={}, user={}, ip={}", code, busModule,
//                    ContextUtil.getCurrentUser().getAccount(), ip);
//        }

        return ResultMsg.SUCCESS(sqlViewVO);
    }

    private void mergeGirdParams2QueryData(JSONObject queryDataObject, SqlViewRequest request) {
        // 针对ag-grid参数特别处理
        if (queryDataObject == null) {
            return;
        }
        JSONObject gridParams = queryDataObject;

        // 检查排序
        String orderBy = "";
        if (gridParams.containsKey("sortModel")) {
            JSONArray sortModel = gridParams.getJSONArray("sortModel");
//            for(int i=0; i<sortModel.size(); i++){
//                JSONObject sortObj = sortModel.getJSONObject(i);
//                orderBy += sortObj.getString("colId")+"#"+sortObj.getString("sort")+",";
//            }
            // 多个排序只取第一个
            if (sortModel != null && sortModel.size() > 0) {
                JSONObject sortObj = sortModel.getJSONObject(0);
                orderBy = sortObj.getString("colId") + "#" + sortObj.getString("sort") + ",";
            }
            gridParams.remove("sortModel");
        }
        if (StringUtil.isNotEmpty(orderBy)) {
            // 不保留原有排序
            String newOrderBy = orderBy; // + queryDataObject.getString("orderBy");
            queryDataObject.put("orderBy", newOrderBy);
        }
        // 检查字段过滤
        // https://www.ag-grid.com/javascript-grid-filter-provided-simple/
        if (gridParams.containsKey("filterModel")) {
            List<JSONObject> listSqlSearchVO = new ArrayList<>();
            Set<String> filterFields = gridParams.getJSONObject("filterModel").keySet();
            for (String field : filterFields) {
                JSONObject filterObj = gridParams.getJSONObject("filterModel").getJSONObject(field);
                String filterType = filterObj.getString("filterType");
                // 多个条件 operator, condition1, condition2
                if (filterObj.containsKey("operator")
                        && filterObj.containsKey("condition1")
                        && filterObj.containsKey("condition2")) {
                    String operator = filterObj.getString("operator");

                    JSONObject condition1 = filterObj.getJSONObject("condition1");
                    JSONObject sqlSearchVo1 = covertSqlSearchVO(field, filterType, condition1);
                    sqlSearchVo1.put("bracket", "(");
                    listSqlSearchVO.add(sqlSearchVo1);

                    JSONObject condition2 = filterObj.getJSONObject("condition2");
                    JSONObject sqlSearchVo2 = covertSqlSearchVO(field, filterType, condition2);
                    sqlSearchVo2.put("bracket", ")");
                    if ("OR".equalsIgnoreCase(operator)) {
                        sqlSearchVo2.put("andOr", "or");
                    }
                    listSqlSearchVO.add(sqlSearchVo2);
                } else {
                    JSONObject sqlSearchVo = covertSqlSearchVO(field, filterType, filterObj);
                    listSqlSearchVO.add(sqlSearchVo);
                }
            }
            if (listSqlSearchVO != null) {
                JSONArray queryData = queryDataObject.getJSONArray("queryData");
                if (queryData == null) {
                    queryData = new JSONArray();
                }
                for (JSONObject searchVo : listSqlSearchVO) {
                    queryData.add(searchVo);
                }
                queryDataObject.put("queryData", queryData);
            }
            gridParams.remove("filterModel");
        }
    }

    private JSONObject covertSqlSearchVO(String field, String filterType, JSONObject conditionObj) {
        JSONObject sqlSearchVo = new JSONObject();
        sqlSearchVo.put("name", field);
        sqlSearchVo.put("type", filterType);
        // type-过滤类型
        String type = conditionObj.getString("type");
        String filter = conditionObj.getString("filter");
        // sql注入检查
        if (StringUtil.isNotEmpty(filter)) {
            filter = filter.replaceAll("'", "");
        }
        sqlSearchVo.put("con", SqlSearchVO.convertCon(type));
        sqlSearchVo.put("val", filter);
        if ("date".equalsIgnoreCase(filterType)) {
            //inRange,dateFrom,dateTo
            if (conditionObj.containsKey("dateFrom")) {
                //  移除时分秒
                java.util.Date date = DateUtil.parse(conditionObj.getString("dateFrom"));
                String formatDate = DateUtil.formatDate(date);
                sqlSearchVo.put("val", formatDate);
                //sqlSearchVo.put("val", conditionObj.getString("dateFrom"));
            }
            if (conditionObj.containsKey("dateTo")) {
                java.util.Date date = DateUtil.parse(conditionObj.getString("dateTo"));
                String formatDate = DateUtil.formatDate(date);
                sqlSearchVo.put("val2", formatDate);
                //sqlSearchVo.put("val2", conditionObj.getString("dateTo"));
            }
        } else if ("number".equalsIgnoreCase(filterType)) {
            //inRange,filter,filterTo
            if (conditionObj.containsKey("filterTo")) {
                sqlSearchVo.put("val2", conditionObj.getString("filterTo"));
            }
        } else if ("text".equalsIgnoreCase(filterType)) {
            //filter
        } else if ("set".equalsIgnoreCase(filterType) || "dict".equalsIgnoreCase(filterType) || "map".equalsIgnoreCase(filterType)) {
            //filter
            sqlSearchVo.put("con", "in");
        }
        return sqlSearchVo;
    }


    public ResultMsg<String> exportData(SqlViewRequest request, HttpServletResponse response) {
        String sheetName = "";

        String code = request.getCode();

        try {
            this.logger.info("自定义列表-数据导出,code:{}", code);

            FormCustSql vo = this.formCustSqlManager.getByCode(code, true);

            List<FormCustSqlFields> settings = ViewUtil.getFields(vo.getFieldsList(), "5");
            if (CollectionUtils.isEmpty(settings)) {
                this.logger.error("找不到可以导出的字段列表,code:{}", code);
                return ResultMsg.ERROR("找不到可以导出的字段列表");
            }
            String fieldColumns = request.getFieldColumns();
            if (StringUtils.isNotEmpty(fieldColumns)) {
                String[] fieldColumnsArray = fieldColumns.split(",");
                List<String> fieldSet = Arrays.asList(fieldColumnsArray);
                Iterator<FormCustSqlFields> iterator = settings.iterator();
                while (iterator.hasNext()) {
                    FormCustSqlFields tmSqlFieldSettingVO = iterator.next();
                    if (!fieldSet.contains(tmSqlFieldSettingVO.getFieldName())) {
                        iterator.remove();
                    }
                }
            }

            sheetName = vo.getName();

            setResponseContext(response, sheetName);

            ServletOutputStream servletOutputStream = response.getOutputStream();


            WritableWorkbook wb = Workbook.createWorkbook((OutputStream) servletOutputStream);

            //fix 打开导出文件 提示 重新命名无效的工作表名称 错误。
            // 名称规则：不能多于31个字符  不能包含关键字 \ / ? * [ ]      不能为空
            WritableSheet sheet = wb.createSheet("Sheet1", 0);  // sheetName->Sheet1


            setSheetHeader(sheetName, settings, sheet, vo);


            setSheetDataList(sheet, settings, request, vo);

            wb.write();
            wb.close();
            servletOutputStream.flush();
            servletOutputStream.close();
            this.logger.info("导出" + sheetName + "列表成功  request=[{}]", request);


            return null;
        } catch (Exception e) {
            this.logger.error("导出" + sheetName + "列表失败 request=[{}] , e : {}", request, e);
            return ResultMsg.ERROR("导出异常:{}" + ExceptionUtil.getRootErrorMseeage(e));
        }
    }


    private void setSheetDataList(WritableSheet sheet, List<FormCustSqlFields> settings, SqlViewRequest request, FormCustSql vo) throws Exception {
        int maxRow = Integer.MIN_VALUE;  //2147483647
        request.setPageSize(maxRow);

        ResultMsg<SqlViewDataVO> data = listData(request);

        SqlViewDataVO sqlViewDataVO = (SqlViewDataVO) data.getData();

        JSONArray list = (JSONArray) sqlViewDataVO.getList();

        int col = 0;
        int row = 2;
        int count = 1;

        Map<String, JSONObject> dicMap = new HashMap<>();  //共享数据字典
        String dateFormat = "yyyy-MM-dd hh:mm:ss";
        jxl.write.DateFormat df = new jxl.write.DateFormat(dateFormat);
        WritableCellFormat wcf = new jxl.write.WritableCellFormat(df);
        Map<String, WritableCellFormat> dateFormatMap = new HashMap<>();
        dateFormatMap.put(dateFormat, wcf);


        for (int i = 0; i < list.size(); i++) {
            JSONObject jo = (JSONObject) list.get(i);
            //根据是否显示序号来判断是否在导出的时候显示序号
            if (vo.getShowRowsNum() == null || vo.getShowRowsNum().equals(1)) {
                sheet.addCell(new Number(col++, row, count));
            }
            count++;
            // Map<String, JSONObject> dicMap = new HashMap<>();

            for (FormCustSqlFields setting : settings) {
                String fieldName = setting.getFieldName();
                String val = jo.getString(fieldName);
                String format = setting.getDateFormat();
                int controlType = setting.getControlType().intValue();


                JSONObject formatObj = getFormatObj(controlType, format, dicMap);

                // dataType: datetime/number/varchar
                val = getValue(controlType, val, format, formatObj);

                //数字格式化 百分比格式化 直接数字去掉小数点后的多余的0 + %
                if (StringUtils.isNotBlank(val) && "percentFormat".equals(format) && NumberUtil.isNumber(val)) {
                    val = NumberUtil.toStr(NumberUtil.toBigDecimal(val)) + "%";
                }
                //数字格式化 直接数字去掉小数点后的多余的0
                if (StringUtils.isNotBlank(val) && "delZero".equals(format) && NumberUtil.isNumber(val)) {
                    val = NumberUtil.toStr(NumberUtil.toBigDecimal(val));
                }

                boolean toNumber = false;
                if ("number".equals(setting.getDataType())
                        && NumberUtil.isNumber(val)
                        && !NumberUtil.isLong(val)
                ) {
                    //Long 类型的一般为id 不转换为数字，避免显示为科学计算法
                    toNumber = true;
                }
                boolean toDate = false;
                if ("datetime".equals(setting.getDataType()) && StringUtil.isNotEmpty(val)) {
                    toDate = true;
                }
                if (toNumber) {
                    sheet.addCell(new Number(col, row, NumberUtil.toBigDecimal(val).doubleValue()));
                } else if (toDate) {
                    // date
                    try {
                        // fix 4491日期根据dateformat格式显示
                        WritableCell cell = new DateTime(col, row, DateUtil.parse(val));
                        dateFormat = "yyyy-MM-dd hh:mm:ss";
                        if (StringUtils.isNotBlank(setting.getDateFormat())) {
                            dateFormat = setting.getDateFormat();
                        }
                        if (!dateFormatMap.containsKey(dateFormat)) {
                            df = new jxl.write.DateFormat(dateFormat);
                            wcf = new jxl.write.WritableCellFormat(df);
                            dateFormatMap.put(dateFormat, wcf);
                        }
                        cell.setCellFormat(dateFormatMap.get(dateFormat));
                        sheet.addCell(cell);
                    } catch (Throwable t) {
                        logger.debug("cell datetime is validate :" + val + ", message:" + t.getMessage());
                        sheet.addCell(new Label(col, row, val));
                    }
                } else {
                    sheet.addCell((WritableCell) new Label(col, row, val));
                }
                col++;
            }

            row++;
            col = 0;
        }
    }


    private String getValue(int controlType, String val, String format, JSONObject formatObj) {
        Date date;
        switch (controlType) {
            case 4:  //下拉
            case 5:  //字典
            case 6:  //单选
            case 7:  //字典单选
                if (formatObj.containsKey(val)) {
                    val = formatObj.getString(val);
                }
                break;
            case 8:  //复选
            case 9:  //字典（复选）
                if (StringUtil.isNotEmpty(val)) {
                    String[] valArray = val.split(",");
                    List<String> valList = new ArrayList<>();
                    for (String tempVal : valArray) {
                        if (formatObj.containsKey(tempVal)) {
                            val = formatObj.getString(tempVal);
                            valList.add(val);
                        }
                    }
                    val = ArrayUtil.join(valList, ",");
                }
                break;
            case 3:  //日期
                date = null;
                if (!StringUtils.isEmpty(val) && val.indexOf("{") >= 0) {
                    formatObj = JSONObject.parseObject(val);
                    date = new Date(formatObj.getLong("time").longValue());
                } else if (StringUtils.isNotEmpty(val)) {
                    if (StringUtils.isNumeric(val)) {
                        date = new Date(Long.parseLong(val));
                    } else {
                        date = DateUtils.parseDate(val);
                    }
                }


                if (BeanUtils.isNotEmpty(date)) {
                    if (StringUtils.isEmpty(format)) {
                        format = "yyyy-MM-dd HH:mm:ss";
                    } else {
                        format = format.replaceAll("hh", "HH");
                    }
                    String dateStr = DateUtils.formatDate(date, format);
                    val = dateStr;
                    break;
                }
                val = "";
                break;
            case 10:  //json
                if (StringUtil.isNotEmpty(val)) {
                    //bug fix
                    val = JSONObjectUtil.getValByKey(val, formatObj);
                }
                break;
            case 18:
                //后端代码表
                if (val == null){
                    val = "";
                    break;
                }
                if (val.contains(",")){
                    String[] vv = val.split(",");
                    List<String> vvs = new ArrayList<>();
                    for (String v : vv){
                        if (formatObj.containsKey(v)) {
                            vvs.add( formatObj.getString(v) );
                        }
                    }
                    if (vvs.isEmpty()){
                        val = "";
                    }else{
                        val = String.join(",", vvs);
                    }
                }else{
                    if (formatObj.containsKey(val)) {
                        val = formatObj.getString(val);
                    } else {
                        val = "";
                    }
                }
                break;

                // TODO 扩展字段控件类型
                // 流程相关列
        }
        return val;
    }

    private JSONObject getFormatObj(int controlType, String format, Map<String, JSONObject> dicMap) {
        JSONObject tempJo = new JSONObject();

        // 4-下拉 6-单选 8-复选, 10-json
        if (Util.list((Object[]) new Integer[]{Integer.valueOf(4), Integer.valueOf(6), Integer.valueOf(8)
        }).contains(Integer.valueOf(controlType))) {
            tempJo = JSONObject.parseObject(format);

            return tempJo;
        }

        // 数据字典 5-字典 7-字典单选 9-字典复选 18-字典-后台
        // 代码表（后端代码表18）
        if (Util.list((Object[]) new Integer[]{Integer.valueOf(5), Integer.valueOf(7), Integer.valueOf(9), Integer.valueOf(18)
        }).contains(Integer.valueOf(controlType))) {

            if (!dicMap.containsKey(format)) {
                List<DataDictDTO> dictNodeList = this.dataDictService.getDictNodeList(format, Boolean.valueOf(false));
                JSONObject dicJo = new JSONObject();
                dicMap.put(format, dicJo);
                if (!CollectionUtils.isEmpty(dictNodeList)) {
                    for (DataDictDTO dic : dictNodeList) {
                        dicJo.put(dic.getKey(), dic.getName());
                    }
                }
            }
            tempJo = dicMap.get(format);
        }

        // TODO 扩展字段控件类型

        return tempJo;
    }


    private void setSheetHeader(String sheetName, List<FormCustSqlFields> settings, WritableSheet sheet, FormCustSql vo) throws WriteException, RowsExceededException {
        sheet.mergeCells(0, 0, settings.size(), 0);


        WritableCellFormat writableCellFormat = JxlUtils.createTitleFormat();
        CellFormat columnFormat = JxlUtils.createColumnFormat();
        sheet.addCell((WritableCell) new Label(0, 0, sheetName, (CellFormat) writableCellFormat));
        int col = 0;
        int row = 1;

        //根据是否显示序号来判断是否在导出的时候显示序号
        if (vo.getShowRowsNum() == null || vo.getShowRowsNum().equals(1)) {
            sheet.addCell(new Label(col++, row, "序号", columnFormat));
        }

        for (FormCustSqlFields setting : settings) {
            sheet.addCell((WritableCell) new Label(col++, row, setting.getFieldDesc(), columnFormat));
        }
    }


    private void setResponseContext(HttpServletResponse response, String sheetName) throws UnsupportedEncodingException {
        // TODO 国际化支持，英文，中日韩
        sheetName = new String(sheetName.getBytes("UTF-8"), "ISO8859-1");
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + sheetName + ".xls");
    }


    public ResultMsg<String> changeData(String code, String buttonAlias, String ids) {
        FormCustSql vo = this.formCustSqlManager.getByCode(code, true);

        String buttons = vo.getButtons();
        if (StringUtil.isEmpty(buttons)) {
            this.logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮不存在");
        }

        List<SqlButtonVO> buttonList = JSONObjectUtil.toList(buttons, SqlButtonVO.class);

        SqlButtonVO btnButtonVO = null;
        for (SqlButtonVO sqlButtonVO : buttonList) {
            if (Util.equals(sqlButtonVO.getAlias(), buttonAlias) &&
                    Util.equals(sqlButtonVO.getClickType(), Integer.valueOf(1))) {
                btnButtonVO = sqlButtonVO;
            }
        }

        if (btnButtonVO == null) {
            this.logger.error("自定义按钮不存在,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮不存在");
        }


        String dataChangeStr = btnButtonVO.getDataChange();
        if (StringUtil.isEmpty(dataChangeStr)) {
            this.logger.error("自定义按钮数值变化字段为空,code:{},btnAlias:{}", code, buttonAlias);
            return ResultMsg.ERROR("自定义按钮数值变化字段为空");
        }


        String tableName = vo.getTableName();
        List<FormCustSqlFields> fieldsList = vo.getFieldsList();


        Map<String, String> fieldNameColumnMap = (Map<String, String>) fieldsList.stream().filter(fields -> Util.equals(fields.getTableName(), tableName)).collect(Collectors.toMap(FormCustSqlFields::getFieldName, FormCustSqlFields::getName));

        List<String> columnList = (List<String>) fieldsList.stream().map(FormCustSqlFields::getName).collect(Collectors.toList());

        String dataChangeSql = "";
        String[] dataChangeArray = dataChangeStr.split(";");
        List<SqlButtonDataChangeVO> dataChangeList = new ArrayList<>();
        for (String dataChange : dataChangeArray) {
            String[] changeData = dataChange.split("=");
            if (changeData.length != 2) {
                this.logger.error("自定义按钮数值变化配置不正确,code:{},btnAlias:{},dataChange:{}", new Object[]{code, buttonAlias, dataChange});
                return ResultMsg.ERROR("自定义按钮数值变化配置不正确:" + dataChange);
            }
            SqlButtonDataChangeVO dataChangeVO = new SqlButtonDataChangeVO();
            String fieldName = changeData[0];
            String value = changeData[1];
            dataChangeVO.setFieldName(fieldName);
            dataChangeVO.setValue(value);

            if (columnList.contains(fieldName)) {
                dataChangeVO.setColumn(fieldName);
            } else if (fieldNameColumnMap.containsKey(fieldName)) {
                String column = fieldNameColumnMap.get(dataChangeVO.getFieldName());
                dataChangeVO.setColumn(column);
            } else {
                this.logger.error("自定义按钮数值变化配置不正确,主表中找不到对应的字段,code:{},btnAlias:{},dataChange:{},column:{}", new Object[]{code, buttonAlias, dataChange, fieldName});

                return ResultMsg.ERROR("自定义按钮数值变化配置不正确,主表中找不到对应的字段:" + dataChange);
            }
            dataChangeSql = dataChangeSql + dataChangeVO.getColumn() + " = " + dataChangeVO.getValue() + ",";
            dataChangeList.add(dataChangeVO);
        }

        ids = ArrayUtil.join((Object[]) ids.split(","), "','");

        dataChangeSql = dataChangeSql.substring(0, dataChangeSql.length() - 1);


        String pkName = fieldNameColumnMap.get(vo.getPkName());

        String updateSql = "update " + tableName + " set " + dataChangeSql + " where " + pkName + " in (" + ids + ")";

        String dsKey = vo.getDsKey();

        JdbcTemplate jdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);

        jdbcTemplate.execute(updateSql);

        return ResultMsg.SUCCESS("操作成功");
    }
}
